<?php

namespace AppBundle\Util;

use AppBundle\Exception\DBException;
use Doctrine\DBAL\Driver\Connection;
use Doctrine\DBAL\Driver\PDOConnection;
use AppBundle\Log\LoggerTrait;
use Psr\Log\LoggerInterface;

/**
 * Description of DBUtil
 *
 * @author maple
 */
class DB2Util
{
    use LoggerTrait;

    const DEFAULT_PAGE_SIZE = 10;
    const PGN_MAX_LIMIT = 4294967295; // 2^31-1
    const OP_EQ = '=';
    const OP_NE = '<>';
    const OP_BW = 'BETWEEN';
    const OP_BN = 'NOT BETWEEN';
    const OP_IN = 'IN';
    const OP_NI = 'NOT IN';
//    const OP_EW = 'WITH';
//    const OP_EN = 'NOT WITH';
    const OP_CN = 'LIKE';
    const OP_NC = 'NOT LIKE';
    const OP_NU = 'NULL';
    const OP_NN = 'NOT NULL';
    const OP_LT = '<';
    const OP_LE = '<=';
    const OP_GT = '>';
    const OP_GE = '>=';
    const OP_RI = '>=&<=';

    const ORDER_DESC = 'DESC';
    const ORDER_ASC = 'ASC';

    /** @var  PDOConnection */
    private $conn;

    /**
     * DB2Util constructor.
     * @param Connection $conn
     * @param LoggerInterface $logger
     */
    public function __construct($conn, $logger)
    {
        $this->conn = $conn;
        $this->setLogger($logger);
    }


    /**
     * @param $conn PDOConnection
     * @param $sql
     * @param array $body
     * @param array $whereParams
     * @param array $orderParams
     * @return mixed
     * @throws DBException
     */
    public function getResult($sql, $body = array(), $whereParams = array(), $orderParams = array())
    {
        try {
            $bindParams = [];
            // 语句类型： SELECT，INSERT，UPDATE，DELETE
            $type = substr(trim(strtoupper($sql)), 0, 6);
            $this->logInfo('[DB2Util getResult] SQL type: ' . $type);

            foreach ($body as $key => $value) {
                if (strpos($sql, ':' . $key . ':')) {
                    $sql = str_replace(':' . $key . ':', ':' . $key . 'custom', $sql);
                    $bindParams[$key . 'custom'] = $value;
                }
            }

            $this->logInfo('[DB2Util getResult] Assembling where start......');
            $where = '';
            foreach ($whereParams as $param) {
                if (!array_key_exists($param['INTERFACE_KEY'], $body)) {
                    if ($param['REQUIRED'] == true) {
                        $this->logError('The request parameteres is invalid. "' . $param['INTERFACE_KEY'] . '" is required');
                        return [
                            'errorCode' => '2001',
                            'errorMsg' => '缺少WHERE条件需要的参数： ' . $param['INTERFACE_KEY']
                        ];
                    }
                    continue;
                }

                $alias = ($param['TABLE_ALIAS'] == null || strlen($param['TABLE_ALIAS']) == 0) ? '' : ($param['TABLE_ALIAS'] . '.');
                switch ($param['OPERATION_CD']) {
                    case self::OP_EQ:
                        $where .= $alias . $param['TABLE_COLUMN'] . '=:' . $param['INTERFACE_KEY'] . ' and ';
                        $bindParams[$param['INTERFACE_KEY']] = $body[$param['INTERFACE_KEY']];
                        break;
                    case self::OP_NE:
                        $where .= $alias . $param['TABLE_COLUMN'] . '<>:' . $param['INTERFACE_KEY'] . ' and ';
                        $bindParams[$param['INTERFACE_KEY']] = $body[$param['INTERFACE_KEY']];
                        break;
                    case self::OP_BW:
                        $where .= $alias . $param['TABLE_COLUMN'] . ' BETWEEN :' . $param['INTERFACE_KEY'] . '11 AND :' . $alias . $param['INTERFACE_KEY'] . '22 and ';
                        $bodyValue = explode(',', $body[$param['INTERFACE_KEY']]);
                        $bindParams[$param['INTERFACE_KEY'] . '11'] = $bodyValue[0];
                        $bindParams[$param['INTERFACE_KEY'] . '22'] = $bodyValue[1];
                        break;
                    case self::OP_BN:
                        $where .= $alias . $param['TABLE_COLUMN'] . ' NOT BETWEEN :' . $param['INTERFACE_KEY'] . '11 AND :' . $alias . $param['INTERFACE_KEY'] . '22 and ';
                        $bodyValue = explode(',', $body[$param['INTERFACE_KEY']]);
                        $bindParams[$param['INTERFACE_KEY'] . '11'] = $bodyValue[0];
                        $bindParams[$param['INTERFACE_KEY'] . '22'] = $bodyValue[1];
                        break;
                    case self::OP_IN:
                        $where .= $alias . $param['TABLE_COLUMN'] . ' IN (:' . $param['INTERFACE_KEY'] . ') and ';
                        $bindParams[$param['INTERFACE_KEY']] = $body[$param['INTERFACE_KEY']];
                        break;
                    case self::OP_NI:
                        $where .= $alias . $param['TABLE_COLUMN'] . ' NOT IN (:' . $param['INTERFACE_KEY'] . ') and ';
                        $bindParams[$param['INTERFACE_KEY']] = $body[$param['INTERFACE_KEY']];
                        break;
                    case self::OP_CN:
                        $where .= $alias . $param['TABLE_COLUMN'] . ' LIKE :' . $param['INTERFACE_KEY'] . ' and ';
                        $bindParams[$param['INTERFACE_KEY']] = '%' . $body[$param['INTERFACE_KEY']] . '%';
                        break;
                    case self::OP_NC:
                        $where .= $alias . $param['TABLE_COLUMN'] . ' NOT LIKE :' . $param['INTERFACE_KEY'] . ' and ';
                        $bindParams[$param['INTERFACE_KEY']] = '%' . $body[$param['INTERFACE_KEY']] . '%';
                        break;
                    case self::OP_NU:
                        $where .= $alias . $param['TABLE_COLUMN'] . ' IS NULL and ';
                        break;
                    case self::OP_NN:
                        $where .= $alias . $param['TABLE_COLUMN'] . ' NOT NULL and ';
                        break;
                    case self::OP_LT:
                        $where .= $alias . $param['TABLE_COLUMN'] . '<:' . $param['INTERFACE_KEY'] . ' and ';
                        $bindParams[$param['INTERFACE_KEY']] = $body[$param['INTERFACE_KEY']];
                        break;
                    case self::OP_LE:
                        $where .= $alias . $param['TABLE_COLUMN'] . '<=:' . $param['INTERFACE_KEY'] . ' and ';
                        $bindParams[$param['INTERFACE_KEY']] = $body[$param['INTERFACE_KEY']];
                        break;
                    case self::OP_GT:
                        $where .= $alias . $param['TABLE_COLUMN'] . '>:' . $param['INTERFACE_KEY'] . ' and ';
                        $bindParams[$param['INTERFACE_KEY']] = $body[$param['INTERFACE_KEY']];
                        break;
                    case self::OP_GE:
                        $where .= $alias . $param['TABLE_COLUMN'] . '>=:' . $param['INTERFACE_KEY'] . ' and ';
                        $bindParams[$param['INTERFACE_KEY']] = $body[$param['INTERFACE_KEY']];
                        break;
                    case self::OP_RI:
                        $where .= $alias . $param['TABLE_COLUMN'] . ' >=:' . $param['INTERFACE_KEY'] . '11 AND ' . $alias . $param['tableKey'] . '<=:' . $param['interfaceKey'] . '22 and ';
                        $bodyValue = explode(',', $body[$param['INTERFACE_KEY']]);
                        $bindParams[$param['INTERFACE_KEY'] . '11'] = $bodyValue[0];
                        $bindParams[$param['INTERFACE_KEY'] . '22'] = $bodyValue[1];
                        break;
                    default:
                        break;
                }
            }

            if (strlen($where) > 0) {
                $where = 'where ' . substr($where, 0, count($where) - 6);
            }
            $sql = str_replace(':where', $where, $sql);
            $this->logInfo('[DB2Util getResult] Assembling where success.');
            $this->logInfo('[DB2Util getResult] SQL with where: ' . $sql);

            $this->logInfo('[DB2Util getResult] Assembling order......');
            $order = '';
            foreach ($orderParams as $param) {
                if (!array_key_exists($param['INTERFACE_KEY'], $body)) {
                    continue;
                }
                $alias = ($param['TABLE_ALIAS'] == null || strlen($param['TABLE_ALIAS']) == 0) ? '' : ($param['TABLE_ALIAS'] . '.');
                if ($body[$param['TABLE_COLUMN']] == self::ORDER_DESC) {
                    $order .= $alias . $param['TABLE_COLUMN'] . ' ' . self::ORDER_DESC . ', ';
                } elseif ($body[$param['INTERFACE_KEY']] == self::ORDER_ASC) {
                    $order .= $alias . $param['TABLE_COLUMN'] . ' ' . self::ORDER_ASC . ', ';
                }
            }

            if (strlen($order) > 0) {
                $order = 'order by ' . substr($order, 0, count($order) - 3);
            }
            $sql = str_replace(':order', $order, $sql);
            $this->logInfo('[DB2Util getResult] Assembling order success.');
            $this->logInfo('[DB2Util getResult] SQL with order: ' . $sql);

            $this->logInfo('[DB2Util getResult] Bind parameteres: ' . json_encode($bindParams));

            // 执行预编译SQL
            if ($type == "INSERT" or $type == "DELETE" or $type == "UPDATE") {
                return $this->returnCUDResult($sql, $bindParams, $type);
            } elseif ($type == "SELECT") {
                if (isset($body['pageSize']) && isset($body['pageNum'])) {
                    return $this->returnRPagenationResult($sql, $bindParams, $body['pageSize'], $body['pageNum']);
                } else {
                    return $this->returnRResult($sql, $bindParams);
                }

            }
        } catch (\Exception $ex) {
            $this->logError('[DB2Util getResult] Has exception, message: ' . $ex->getMessage());
            throw $ex;
        }
    }

    /**
     * 返回INSERT, UPDATE, DELETE类型的sql执行结果
     * @param $sql
     * @param $bindParams
     * @param $type
     * @return array
     */
    private function returnCUDResult($sql, $bindParams, $type)
    {
        $sth = $this->conn->prepare($sql);
        foreach ($bindParams as $key => $param) {
            $sth->bindValue($key, $param);
        }
        $result = $sth->execute();
        if (!$result) {
            return $this->returnError2002($sth->errorCode());
        }

        $res = $sth->rowCount();
        return [
            'errorCode' => '0000',
            'errorMsg' => 'success',
            $type => $res
        ];
    }

    /**
     * 返回SELECT类型的sql执行结果
     * @param $sql
     * @param $bindParams
     * @return array
     */
    public function returnRResult($sql, $bindParams)
    {
        $sth = $this->conn->prepare($sql);
        foreach ($bindParams as $key => $param) {
            $sth->bindValue($key, $param);
        }
        $result = $sth->execute();
        if (!$result) {
            return $this->returnError2002($sth->errorCode());
        }
        $res = $sth->fetchAll();

        return [
            'errorCode' => '0000',
            'errorMsg' => 'success',
            'result' => $res
        ];
    }

    /**
     * 返回SELECT类型sql带分页参数的执行结果
     * @param $sql
     * @param $bindParams
     * @param $pageSize
     * @param $pageNum
     * @return array
     */
    public function returnRPagenationResult($sql, $bindParams, $pageSize, $pageNum)
    {
        $limitSql = '
            SELECT A.* 
            FROM (SELECT B.*, ROWNUMBER() OVER() AS RN FROM (' . $sql . ') AS B)AS A 
            WHERE A.RN BETWEEN ' . ($pageNum - 1) * $pageSize . ' AND ' . $pageNum * $pageSize;

        $totalSql = 'SELECT COUNT(1) FROM (' . $sql . ')';

        $cntSth = $this->conn->prepare($totalSql);
        foreach ($bindParams as $key => $param) {
            $cntSth->bindValue($key, $param);
        }
        $result = $cntSth->execute();
        if (!$result) {
            return $this->returnError2002($cntSth->errorCode());
        }
        $cnt = $cntSth->fetch();
        $total = intval($cnt[1]);
        if ($total == 0) {
            return [
                'errorCode' => '0000',
                'errorMsg' => 'success',
                'result' => [],
                'totalRows' => 0,
                'totalPages' => 0
            ];
        }

        $sth = $this->conn->prepare($limitSql);
        foreach ($bindParams as $key => $param) {
            $sth->bindValue($key, $param);
        }
        $result = $sth->execute();
        if (!$result) {
            return $this->returnError2002($sth->errorCode());
        }
        $res = $sth->fetchAll();

        return [
            'errorCode' => '0000',
            'errorMsg' => 'success',
            'result' => $res,
            'totalRows' => $total,
            'totalPages' => $total % $pageSize == 0 ? $total / $pageSize : ceil($total / $pageSize)
        ];
    }

    private function returnError2002($errorCode)
    {
        $this->logError('[DBUtil getResult] Execute sql failed, error code: ' . $errorCode);
        return [
            'errorCode' => '2002',
            'errorMsg' => 'SQL预编译语句执行错误'
        ];
    }

}
